// 统计分析 赵

var express = require('express');
var router = express.Router();
const db = require('../../db/db');


// 牧畜统计
// 获取牧畜大总类和数量
router.get('/getMchNum', function (req, res) {
  const { orgId, houseId, times } = req.query;

  const conditions = [];
  const params = [];
  console.log(times)
  
  // 解析时间参数
  let timeRange = [];
  try {
    timeRange = times ? JSON.parse(times) : [];
  } catch (e) {
    return res.cc(0,'时间参数格式错误');
  }

  // 构建查询条件
  if (orgId) {
    conditions.push('F_Org_Id = ?');
    params.push(orgId);
  }
  
  if (houseId) {
    conditions.push('F_HouseId = ?');
    params.push(houseId);
  }

  if (timeRange.length === 2) {
    conditions.push('(F_CreateTime <= ? AND F_Bdtime >= ?)');
    params.push(timeRange[1], timeRange[0]); // 结束时间在前，开始时间在后
  }

  // 构建完整SQL
  let sql = `
    SELECT 
      F_Type_Name,
      COUNT(*) AS num
    FROM v_livestockinfo
  `;

  if (conditions.length > 0) {
    sql += ' WHERE ' + conditions.join(' AND ');
  }

  sql += ' GROUP BY F_Type_Name';

  // 执行查询
  db.query(sql, params, function (err, result) {
    if (err) return res.cc(err);
    if  (result.length === 0) return res.cc(0, '无数据');
    res.cc(200, '获取信息成功', result);
  });
});


// 牧畜数量统计
router.get('/livestocknum', (req, res) => { 
  const { orgId, houseId, times } = req.query;
  const conditions = [];
  const params = [];
  
  // 解析时间参数（兼容空值）
  let timeRange = [];
  try {
    timeRange = times ? JSON.parse(times) : [];
  } catch (e) {
    return res.cc(0,'时间参数格式错误');
  }

  // 构建查询条件
  if (orgId) {
    conditions.push('F_Org_Id = ?');
    params.push(orgId);
  }
  if (houseId) {
    conditions.push('F_HouseId = ?');
    params.push(houseId);
  }

  // 时间范围条件（仅在传入有效时间时添加）
  if (timeRange.length === 2) {
    conditions.push('F_CreateTime BETWEEN ? AND ?');
    params.push(timeRange[0], timeRange[1]);
  }

  // 构建按月统计的SQL
  let sql = `
    SELECT 
      DATE_FORMAT(F_CreateTime, '%Y-%m') AS time,
      SUM(CASE WHEN F_State = 1 THEN 1 ELSE 0 END) AS yangzhinum,
      SUM(CASE WHEN F_State = 2 THEN 1 ELSE 0 END) AS xiaoshounum,
      SUM(CASE WHEN F_State = 3 THEN 1 ELSE 0 END) AS siwangnum
    FROM v_livestockinfo
  `;

  if (conditions.length > 0) {
    sql += ' WHERE ' + conditions.join(' AND ');
  }
  sql += ' GROUP BY time ORDER BY time';

  // 执行查询
  db.query(sql, params, function (err, result) {
    if (err) return res.cc(0, err);
    
    // 自动填充月份逻辑（仅在有时间参数时生效）
    let data = [...result]; 
    if (timeRange.length === 2) {
      const months = [];
      const start = new Date(timeRange[0]);
      const end = new Date(timeRange[1]);
      const current = new Date(start);
      current.setDate(1);
      
      while (current <= end) {
        const year = current.getFullYear();
        const month = String(current.getMonth() + 1).padStart(2, '0');
        months.push(`${year}-${month}`);
        current.setMonth(current.getMonth() + 1);
      }

      const monthlyData = {};
      result.forEach(row => (monthlyData[row.time] = row));
      
      data = months.map(month => ({
        time: month,
        yangzhinum: monthlyData[month]?.yangzhinum || 0,
        xiaoshounum: monthlyData[month]?.xiaoshounum || 0,
        siwangnum: monthlyData[month]?.siwangnum || 0
      }));
    }

    // 计算总数（兼容两种场景）
    const totals = data.reduce((acc, item) => ({
      total: acc.total + item.yangzhinum + item.xiaoshounum + item.siwangnum,
      aquacultureNum: acc.aquacultureNum + item.yangzhinum,
      salesNum: acc.salesNum + item.xiaoshounum,
      deathNum: acc.deathNum + item.siwangnum
    }), { total:0, aquacultureNum:0, salesNum:0, deathNum:0 });

    res.cc(200, '获取信息成功', {
      ...totals,
      data: data.map(d => ({ ...d, time: d.time.replace(/-0/, '-') })) // 修正月份格式
    });
  });
});


// 生长溯源
// 生长溯源信息获取
router.get('/growData', (req, res) => {
  const { pageSize, pageNum, orgId, houseId, F_IMEI } = req.query;

  // 参数处理
  const parsedPageSize = parseInt(pageSize) || 5;
  const parsedPageNum = parseInt(pageNum) || 1;
  const offset = (parsedPageNum - 1) * parsedPageSize;

  // 动态构造 WHERE 子句 [1,4](@ref)
  const conditions = [];
  const params = [];

  if (orgId) {
    conditions.push('F_Org_Id = ?');
    params.push(orgId);
  }
  if (houseId) {
    conditions.push('F_HouseId = ?');
    params.push(houseId);
  }
  if (F_IMEI) {
    conditions.push('F_IMEI = ?');
    params.push(F_IMEI);
  }

  // 基础SQL语句
  let sql = `
    SELECT
    F_Id,
      F_Headimg as image,
      F_IMEI as imei,
      F_Title as title,
      F_Org_Name as orgName,
      F_House_Name as houseName,
      F_Birthday as birthday,
      F_Sex as sex,
      F_Csweight as csweight,
      F_Jy as jy
    FROM v_livestockinfo`;

  // 添加动态条件
  if (conditions.length > 0) {
    sql += ' WHERE ' + conditions.join(' AND ');
  }

  // 添加分页参数 [2](@ref)
  sql += ' LIMIT ?, ?';
  params.push(offset, parsedPageSize);

  // 执行查询（使用参数化查询防止SQL注入）
  db.query(sql, params, (err, result) => {
    if (err) return res.cc(0,err);
    if (result.length === 0) return res.cc(200, '无数据');
    res.cc(200, '获取信息成功', result);
  });
});


// 获取基本信息
router.get('/getBaseInfo', function (req, res) { 
  const { F_IMEI } = req.query;
  if  (!F_IMEI) return res.cc(0,'参数错误');
  const sql = `SELECT 
    F_Headimg as image,
    F_IMEI as imei,
    F_Title as title,
    F_Type_Name as typeName,
    F_House_Name as houseName,
    F_Birthday as birthday,
    F_Sex as sex,
    F_Csweight as csweight,
    F_Weight as weight,
    F_Fathor as fathor,
    F_Mathor as mother,
    F_Jy as jy,
    F_JkState as jkState,
    F_State as state
  FROM v_livestockinfo  -- 确保表名正确
  WHERE F_IMEI = ?`;
  
  db.query(sql, [F_IMEI], (err, result) => {
    if (err) return res.cc(0, err)
    res.cc(200, '查询成功', result)
  });
});

// 获取检疫信息
router.get('/getQuarantine', (req, res) => {

  const { F_Id } = req.query;
  if  (!F_Id) return res.cc(0, '参数错误');
  const sql = `select 
  F_StTime as stTime,
  F_Title as title,
  F_UserName as userName,
  F_Phone as phone,
  F_Stand as stand,
 F_Address as address,
  F_Level as level
  from nc_quarantine where F_liveid = ?`
  db.query(sql, F_Id, (err, result) => {
    if (err) return res.cc(1, err);
    if (result.length === 0) return res.cc(200, '无检疫数据');
    res.cc(200, '查询成功', result)
  });
});


// 获取疫苗记录
router.get('/vaccine', (req, res) => {
  let { F_Id } = req.query;
  if (!F_Id) return res.cc(0,'参数错误');
  const sql = `select 
  F_JzTime as jzTime,
  F_Title as title,
  F_Batch as batch,
  F_Name as name,
  F_PHone as phone,
 F_Number as number,
  F_Address as address
  from nc_vaccine_log where F_liveid = ?`
  db.query(sql, [F_Id], (err, result) => {
    if (err) return res.cc(1,err);
    if (result.length === 0) return res.cc(200,'暂无数据')
    res.cc(200, '查询成功', result)
  });
});

// 获取繁殖记录
router.get('/getReproduce', (req, res) => {
  const { F_Id } = req.query;
  if (!F_Id) return res.cc(0,'参数错误');
  const sql = `select 
  F_Title as title,
  F_EsTimateTime as esTimateTime,
  F_SjTimateTime as sjTimateTime,
  F_Opert as opert,
  F_PHone as phone,
  F_Address as address
  from nc_growth_log where F_liveid = ?`
  db.query(sql, [F_Id], (err, result) => {
    if (err) return res.cc(1, '服务器错误')
    if (result.length === 0) return res.cc(200, '暂无数据')
    res.cc(200, '查询成功', result)
  })
});


// 警告记录
router.get('/getWarning', (req, res) => { 
  let { pageSize, pageNum, key } = req.query;

  // 分页参数处理
  const parsedPageSize = parseInt(pageSize) || 5;
  const parsedPageNum = parseInt(pageNum) || 1;
  const offset = (parsedPageNum - 1) * parsedPageSize;

  // 基础 SQL 语句
  let sql = `
    SELECT
      a.id,
      a.alarmtime, 
      a.alarmtitle, 
      a.sbtype, 
      a.alarmtype, 
      v.F_IMEI as imei, 
      v.F_Title as title
    FROM nc_alarminfo a
    LEFT JOIN v_livestockinfo v ON a.liveid = v.F_Id
  `;

  // 动态 WHERE 条件
  let whereConditions = [];
  let queryParams = [];

  if (key) {
    // 模糊查询字段列表（来自两个表的字段）
    const searchFields = [
      'a.alarmtime', 
      'a.alarmtitle', 
      'a.sbtype', 
      'a.alarmtype',
      'v.F_IMEI',
      'v.F_Title'
    ];

    // 生成条件语句
    whereConditions = searchFields.map(field => `${field} LIKE ?`);
    queryParams = Array(searchFields.length).fill(`%${key}%`);
  }

  // 组合完整 SQL
  if (whereConditions.length > 0) {
    sql += ' WHERE ' + whereConditions.join(' OR ');
  }

  // 添加分页
  sql += ' LIMIT ? OFFSET ?';
  queryParams.push(parsedPageSize, offset);

  // 执行查询
  db.query(sql, queryParams, (err, result) => {
    if (err) return res.cc(1, '服务器错误');
    if (result.length === 0) return res.cc(200, '暂无数据');
    res.cc(200, '查询成功', result);
  });
});

// 删除警告信息
router.delete('/deleteWarning', (req, res) => {
  const { ids } = req.query; // 接收逗号分隔的 ID 字符串（如 "1,2,3"）
  
  // 参数验证
  if (!ids) return res.cc(0, '参数错误: 缺少 ids');
  const idList = ids.split(',').map(Number);

  // 动态生成占位符（如 "?,?,?"）
  const placeholders = idList.map(() => '?').join(',');
  const sql = `DELETE FROM nc_alarminfo WHERE id IN (${placeholders})`;

  db.query(sql, idList, (err, result) => {
    if (err) return res.cc(0, '服务器错误');
    if (result.affectedRows === 0) return res.cc(200, '删除失败: 未找到对应记录');
    res.cc(200, `删除成功`);
  });
});









module.exports = router;